Frequently asked Interview Questions and Answers of QlikView
Explain Qlikview architecture
The QlikView Business Discovery platform consists of 3 major components – QlikView
Server, QlikView Publisher and QlikView Desktop, each playing an important part in
designing, developing and implementing almost every QlikView deployment
Qlikview Desktop
The QlikView Desktop is a Windows-based desktop tool that is used by business analysts
and developers to create a data model and to lay out the graphical user interface (GUI or
presentation layer) for QlikView apps. It is within this environment where a developer will use
a SQL-like scripting environment (augmented by ‘wizards’) to create the linkages (connection
strings) to the source data and to transform the data (e.g. rename fields, apply expressions)
so that it can be analyzed and used within the UI, as well as re-used by other QlikView files.
The QlikView Desktop is also the environment where all user interface design and user
experience is developed in a drag-and-drop paradigm: everything from graphs and tables
containing slices of data to multi-tab architectures to application of color scheme templates
and company logos is done here.
The file type that is created using the QlikView Desktop is known as a QVW (.qvw, or
QlikView file). Upon reload, a QVW can be used to create a data-only QVD (QlikView data)
file, which is binary and contains no UI.
Qlikview Server (Qvs)
The QVS is a server-side product that contains the in-memory analytics engine and
which handles all client/server communication between a QlikView client (i.e. desktop, IE
plugin, AJAX or Mobile) and the server. It includes a management environment (QlikView
Management Console) for providing administrator access to control all aspects of the server
deployments (including security, clustering, distribution etc.) and also includes a web server
to provide front-end access to the documents within. The web server’s user portal is known
as Access Point. (It’s important to note that while the QVS contains its own web server,
one can also utilize Microsoft IIS (Internet Information Server) for this purpose, too). The
QVS handles client authorization against existing directory providers (e.g. Microsoft Active
Directory, eDirectory) and also performs read and write to ACLs (access control lists) for
QVW documents.
Qlikview Publisher
The QlikView Publisher is a server-side product that performs two main functions:
1) It is used to load data directly from data sources defined via connection strings in the
source QVW files.
2) It is also used as a distribution service to reduce data and applications from source QVW
files based on various rules (such as user authorization or data access privileges) and to
distribute these newly-created documents to the appropriate QlikView Servers or
as static PDF reports via email.
Data sources that can be readily accessed by QlikView include standard ODBC or OLEDB compliant
databases, standard flat files such as Microsoft Excel, XML, etc. as well as from
systems such as SAP NetWeaver, Salesforce.com, and Informatica.
Set analysis in qlikview
QlikView set analysis is used when you want to create a set or a group of information independent of your current selection. In other words, a data set that has no influence of your current selections.
Sets can be used in aggregation functions. Aggregation functions normally aggregate over the set of possible records defined by the current selection.
For those of you who know SQL, set analysis is just like SELECT…. GROUP BY statements.
Qlikview Set analysis starts and ends with a curly bracket: {}
E.g.
•Qlikview Set analysis begins and ends with { }
•Modifier begins and ends with < >
•Operator: =
•Identifier is not shown but it is $ which is the default state in Qlikview
Qlikview Set analysis IDENTIFIERS:
Identifier defines the state of the set.
$ is the default state, meaning the current selection for a given sheet.
1 ignores the current selection and considers all the values in the data set.
Bookmark10 state defined by a stored bookmark. If you are new, please review this article to learn more about book marks.
Now, the fun begins…
You can subtract one state from another. For example,
1 – $ Selects all values from the data set but the current selection.
1- Bookmark10 Selects all values from the data set but those defined by a given bookmark.
Qlikview Set analysis MODIFIERS:
Identifier is like a SELECT keyword and Modifier is like a WHERE clause or a filter in your SQL select statement. It works on the selected data set — based on your identifier — to add, remove or to modify existing selections.
Modifiers start with < and end with >. In this example, the returned data set gets modified to filter Year with 2005 and 2006 values. Thus, the resulting data set contains only data for those 2 years.
If you want to do a year vs year analysis, modifier allows you to select data set for the previous year. Like identifier, modifier can make otherwise complicated programming a simple task. For instance, if you want to ignore certain field from your data set, you can simply add a modifier with <Field=>.
Qlikview Set analysis OPERATORS:
Operators are like executors of the Qlikview set analysis. Operators somewhat control behaviour of modifier statements. They are like a method of an object as they allow interactions between two different data sets in a single expression.
Example: sum({$ + Year20052006} TotalDue)
What is Synthetic key and how to avoid it in QlikView?
It is undesirable to have multiple common keys across multiple tables in a QlikView data structure. This may cause QlikView to use complex keys (a.k.a. synthetic keys) to generate the connections in the data structure. Synthetic keys are generally resource heavy and may slow down calculations and, in extreme cases, overload anapplication. They also make a document harder to understand and maintain. Thereare some cases where synthetic keys cannot be avoided (e.g. Interval Match tables),but, in general, synthetic keys should always be eliminated, if possible.
1. comment the fileds in load script
2. rename the fileds in load script
3. rename the fileds using UNQUALIFY operator;
Difference between KEEP and JOIN in QlikView?
Left Keep and Left join gives the same output. The only difference is that Left Keep keeps the Table separate in Data Model, whereas Left join merges the Tables that are joined.
Difference between simple table and pivot table in QlikView?
Pivot table-
1) A pivot table is better at grouping: you can easily see which group a specific row belongs to, and a group can have a subtotal.
2) You can also display a pivot table like a cross table (one or several horizontal dimensions).
3) But when you sort a pivot table, you have to sort it first according to the first dimension, then according to the next, etc. You cannot sort it any way you want.
Straight table-
A straight table is better at sorting than a pivot table: you can sort it according to any column.
But it is not so good at grouping. Subtotals are not possible, for instance.
Which graph will you used for two years difference sale?
BAR Graph
What is Incremental Load in qlikview?
As BI apps are expected to deal with larger and larger amounts of data the amount of time that it takes to retrieve that data becomes a serious issue. This could be due to shear volume of data or the need for frequent refreshes. Either way, you do not want to be pulling all of the data all of the time. What you want to be able to do is just pull the data that has changed, append to that the data that you stored away previously and then get back to the business of analysing. This will reduce load on the source database, the network infrastructure and your QlikView server.
What is Inline memory in QlikView?
Create table or add field to table.
What is Set and let in QlikView and difference between it?
SET or a LET statement is often used To define the variable. The SET statement is used when you want a variableto hold the string or numeric value that is to the right of the Equal (=) sign.
The LET statement is used when you need to evaluate what is to the right of the Equal sign
e.g
set myVar=5*2 the result is “5*2″
Let myVar=5*2 the result is “10″
Explain QlikView Resident Load?
Loading data from already loaded Qlikview table is possible using Resident Load.
Transformations and Calculations can be performed in the resident load script.
Loading a existing field or a succeeding table is possible too.
Example:
Employees:
Select Empname,
HireDate,
Salary,
Incentives
From Employee;
Load Empname,
Month(HireDate),
Salary + Incentives as ‘GrossSalary’
Resident Employees;
What is Apply Map (MAPPING TABLES) in QlikView?
Sometimes you need to add an extra field to a table to use a combination of fields
from different tables, or you want to add a field to clean up the data structure. Qlik-
View has an effective way to add single fields to a table called mapping tables. In this
chapter,
syntax — mapping ( load statement | select statement )
applymap( ‘mapname’, expr, [ , defaultexpr ] )
The QlikView Peek function has the following syntax:
Peek( ‘fieldname’ [ , row [ , ‘tablename’ ] ] ).
Qlikview Data (Qvd) Files?
One of the most important features in writing scripts within QlikView is the use of QlikView Data (QVD) files. A QVD file contains a table of data exported from Qlik-
View. QVD is a native QlikView format. It can only be written to and read from QlikView. The file format is optimized for speed when reading data from a QlikView
script but it is also very compact.
store [(*|<field_list>) from] <table> into <file_name>;
Optimized and unoptimized QVD Load Situations?
Loading the data without performing any transformations is unoptimized load. Loading the data with some transformations is Optimized load. Ex : Load * from tab1 where Year = 2014;
What are the restrictions of Binary Load?
Binary load should be used only once in qvw application and it should be the first line in script editor page.
How do you optimize QlikView Application? (What tools are used and where do you start?
For Optimization we can optimized the QVD which is used in the QVW files in the backend. For QVW optimization i believe not use any tools we can manually remove unwanted fields form data model or comment it, remove most complex logics from the load script or create it with the simple way because it will take long time for loading. If using large data base file try to create the incremental load for the QVD or load latest data only.
What is the difference between Subset ratio & Information Density?
Information Density: is the number of records that have values (i.e. not NULL) in this field as compared to the total number of records in the table.
SubSet Ratio:Subset ratio, which shows the percentage of all distinct values for a field in the table compared to all the distinct values for that field in the entire data model.
What is Dimensions ( What is difference between Expression and Dimension)?
Each data warehouse consists of dimensions and measures. Dimensions allow data analysis from various perspectives. For example, time dimension could show you the breakdown of sales by year, quarter, month, day and hour. Product dimension could help you see which products bring in the most revenue. Supplier dimension could help you choose those business partners who always deliver their goods on time. Customer dimension could help you pick the strategic set of consumers to whom you’d like to extend your very special offers.
Preceding Load in QlikView.
The preceding load gives you the advantage of working with the QlikView syntax instead of the SQL syntax.
What is Dimensions – > Each data warehouse consists of dimensions and measures. Dimensions allow data analysis from various perspectives. For example, time dimension could show you the breakdown of sales by year, quarter, month, day and hour. Product dimension could help you see which products bring in the most revenue. Supplier dimension could help you choose those business partners who always deliver their goods on time. Customer dimension could help you pick the strategic set of consumers to whom you’d like to extend your very special offers.
Explain about Normalized Data?
Well Structured Form of Data, which doesnt have any repetition or redundancy of data. Its a kind of Relational data. Its mainly used in OLTP kind of stuffs
Denormalized Data – Its a whole bunch of data without any relationship among themselves, with redundancy of data. Its mainly used in OLAP kind of stuffs.
What Is Star Sechma?
A star schema is the simplest form of dimensional model, in which data is organized into facts and dimensions. A fact is an event that is counted or measured, such as a sale or login. A dimension contains reference information about the fact, such as date, product, or customer. A star schema is diagramed by surrounding each fact table with its associated dimensions table. The output diagram resembles a star.
What is Snowflaking Schema?
Ans.Snowflaking is a form of dimensional modeling, dimensions are stored in multiple relational dimension tables. A snowflake schema is a variation of the star schema. Snowflaking is used to improve the performance of specific queries. The schema is diagramed with each fact surrounded by its associated dimensions as in a star schema, and those dimensions are further related to other dimensions, branching out into a snowflake pattern.
What is binary load?
Binary load is loading data from another QV file. For example, you have application A.qvw. You can create another application B.qvw with script binary A.qvw. binary file where:
file ::= [ path ] filename
Examples:
Binary customer.qvw;
Binary c:qvcustomer.qvw;
The path is the path to the file, either absolute, or relative to the .qvw file containing this script line.
What is Container ?
A container object can be used to hold multiple charts. You can use a container object to put multiple charts in the same box. All charts will appear in the same window but only one chart will appear active at a given time. You can click the chart title to switch or toggle between charts. A word of advice: Use containers with caution. They form linked objects and might affect the properties of all linked objects.
What is Circular references and how to avoid it. in QlikView?
It is undesirable to have multiple common keys across multiple tables in a QlikView data structure. This may cause QlikView to use Circular references to generate the connections in the data structure. Circular references are generally resource heavy and may slow down calculations and, in extreme cases, overload an application.
- comment the fileds in load script
2. rename the fileds in load script
3. rename the fileds using UNQUALIFY operator.
What is the use of FirstSortValue in QlikView?
SET or a LET statement is often used To define the variable. The SET statement is used when you want a variable to hold the string or numeric value that is to the right of the Equal (=) sign.
The LET statement is used when you need to evaluate what is to the right of the Equal sign
e.g
set myVar=52 the result is 52″
Let myVar=5*2 the result is 10″